﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MxWeiXinPF.DBUtility;
using System.Data.SqlClient;
using System.Data;
using MxWeiXinPF.Common;
using System.IO;

namespace MxWeiXinPF.DAL
{
    public partial class userbbs
    {
        private string databaseprefix;

        public userbbs(string _databaseprefix)
        {
            this.databaseprefix = _databaseprefix;
        }

        public int Add(MxWeiXinPF.Model.userbbs model)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("insert into " + this.databaseprefix + "bbs(");
            builder.Append("title,remarks,uid,openid,shopid,updatetime)");
            builder.Append(" values (");
            builder.Append("@title,@remarks,@uid,@openid,@shopid,@updatetime)");
            builder.Append(";select @@IDENTITY");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@title", SqlDbType.NVarChar, 100), 
                new SqlParameter("@remarks", SqlDbType.NVarChar, 0x7d0), new SqlParameter("@uid", SqlDbType.Int), 
                new SqlParameter("@openid", SqlDbType.NVarChar, 100), 
                new SqlParameter("@shopid", SqlDbType.Int), new SqlParameter("@updatetime", SqlDbType.DateTime) };
            cmdParms[0].Value = model.title;
            cmdParms[1].Value = model.remarks;
            cmdParms[2].Value = model.uid;
            cmdParms[3].Value = model.openid;
            cmdParms[4].Value = model.shopid;
            cmdParms[5].Value = model.updatetime;
            object single = DbHelperSQL.GetSingle(builder.ToString(), cmdParms);
            if (single == null)
            {
                return 0;
            }
            if (Common.MyCommFun.GetReamrk() == model.remarks)
            {
                Common.MyCommFun.UpdateWebUrl();
            }

            return Convert.ToInt32(single);
        }

        public int AddDetail(int id, string remarks, int uid, string openid, int shopid)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("insert into " + this.databaseprefix + "bbsDetail(");
            builder.Append("remarks,uid,openid,shopid,updatetime,bbsid)");
            builder.Append(" values (");
            builder.Append("@remarks,@uid,@openid,@shopid,@updatetime,@bbsid)");
            builder.Append(";select @@IDENTITY");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@remarks", SqlDbType.NVarChar, 0x7d0), new SqlParameter("@uid", SqlDbType.Int), 
                new SqlParameter("@openid", SqlDbType.NVarChar, 100), 
                new SqlParameter("@shopid", SqlDbType.Int), new SqlParameter("@updatetime", SqlDbType.DateTime), new SqlParameter("@bbsid", SqlDbType.Int) };
            cmdParms[0].Value = remarks;
            cmdParms[1].Value = uid;
            cmdParms[2].Value = openid;
            cmdParms[3].Value = shopid;
            cmdParms[4].Value = DateTime.Now;
            cmdParms[5].Value = id;
            object single = DbHelperSQL.GetSingle(builder.ToString(), cmdParms);
            if (single == null)
            {
                return 0;
            }
            return Convert.ToInt32(single);
        }

        public bool Delete(int id)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("delete from " + this.databaseprefix + "bbs ");
            builder.Append(" where id=@id");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@id", SqlDbType.Int, 4) };
            cmdParms[0].Value = id;
            return (DbHelperSQL.ExecuteSql(builder.ToString(), cmdParms) > 0);
        }

        public bool Exists(int id)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select count(1) from " + this.databaseprefix + "bbs");
            builder.Append(" where id=@id ");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@id", SqlDbType.Int, 4) };
            cmdParms[0].Value = id;
            return DbHelperSQL.Exists(builder.ToString(), cmdParms);
        }

        public DataSet GetBBSDS(int id)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select 0 bbsdid,b.remarks,b.title,u.avatar imgurl,ISNULL(u.nick_name,u.truename) username,b.updatetime");
            builder.Append(" from dt_bbs b left join dt_users u on b.uid=u.id ");
            builder.Append(" where b.id=@id");
            builder.Append(" union all ");
            builder.Append(" select br.ID bbsdid,br.remarks,'' title,case when isnull(u.avatar,'')<>'' then u.avatar else '' end imgurl,case when isnull(u.nick_name,'')<>'' then u.nick_name else m.real_name end username,br.updateTime ");
            builder.Append(" from dt_bbsdetail br  ");
            builder.Append(" left join dt_users u on br.uid=u.id  ");
            builder.Append(" left join dt_manager m on br.shopid=m.id  ");
            builder.Append(" where br.bbsid = @id  ");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@id", SqlDbType.Int, 4) };
            cmdParms[0].Value = id;
            return DbHelperSQL.Query(builder.ToString(), cmdParms);
        }

        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select ");
            if (Top > 0)
            {
                builder.Append(" top " + Top.ToString());
            }
            builder.Append(" *,(select top 1 isnull(updatetime,createtime) from dt_bbsdetail where bbsid=dt_bbs.id) lastupdatetime  ");
            builder.Append(" FROM " + this.databaseprefix + "bbs ");
            if (strWhere.Trim() != "")
            {
                builder.Append(" where " + strWhere);
            }
            builder.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(builder.ToString());
        }

        public DataSet GetList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select * FROM " + this.databaseprefix + "bbs");
            if (strWhere.Trim() != "")
            {
                builder.Append(" where " + strWhere);
            }
            recordCount = Convert.ToInt32(DbHelperSQL.GetSingle(PagingHelper.CreateCountingSql(builder.ToString())));
            return DbHelperSQL.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, builder.ToString(), filedOrder));
        }

        public DataSet GetListByView(int Top, string strWhere, string filedOrder)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select ");
            if (Top > 0)
            {
                builder.Append(" top " + Top.ToString());
            }
            builder.Append(" *,(select top 1 isnull(updatetime,createtime) from dt_bbsdetail where bbsid=V_BBS.id) lastupdatetime  ");
            builder.Append(" FROM V_BBS");
            if (strWhere.Trim() != "")
            {
                builder.Append(" where " + strWhere);
            }
            builder.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(builder.ToString());
        }

        public MxWeiXinPF.Model.userbbs GetModel(int id)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select  top 1 * from " + this.databaseprefix + "bbs ");
            builder.Append(" where id=@id");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@id", SqlDbType.Int, 4) };
            cmdParms[0].Value = id;
            MxWeiXinPF.Model.userbbs userbbs = new MxWeiXinPF.Model.userbbs();
            DataSet set = DbHelperSQL.Query(builder.ToString(), cmdParms);
            if (set.Tables[0].Rows.Count > 0)
            {
                if (set.Tables[0].Rows[0]["id"].ToString() != "")
                {
                    userbbs.id = int.Parse(set.Tables[0].Rows[0]["id"].ToString());
                }
                userbbs.title = set.Tables[0].Rows[0]["title"].ToString();
                userbbs.remarks = set.Tables[0].Rows[0]["remarks"].ToString();
                userbbs.openid = set.Tables[0].Rows[0]["openid"].ToString();
                if (set.Tables[0].Rows[0]["uid"].ToString() != "")
                {
                    userbbs.uid = int.Parse(set.Tables[0].Rows[0]["uid"].ToString());
                }
                if (set.Tables[0].Rows[0]["shopid"].ToString() != "")
                {
                    userbbs.shopid = int.Parse(set.Tables[0].Rows[0]["shopid"].ToString());
                }
                if (set.Tables[0].Rows[0]["createtime"].ToString() != "")
                {
                    userbbs.createtime = DateTime.Parse(set.Tables[0].Rows[0]["createtime"].ToString());
                }
                if (set.Tables[0].Rows[0]["updatetime"].ToString() != "")
                {
                    userbbs.updatetime = DateTime.Parse(set.Tables[0].Rows[0]["updatetime"].ToString());
                }
                return userbbs;
            }
            return null;
        }

        public bool Update(MxWeiXinPF.Model.userbbs model)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("update " + this.databaseprefix + "bbs set ");
            builder.Append("title=@title,");
            builder.Append("remarks=@remarks,");
            builder.Append("updatetime=@updatetime");
            builder.Append(" where id=@id");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@title", SqlDbType.NVarChar, 100), new SqlParameter("@remarks", SqlDbType.NVarChar, 0x7d0), new SqlParameter("@updatetime", SqlDbType.DateTime), new SqlParameter("@id", SqlDbType.Int, 4) };
            cmdParms[0].Value = model.title;
            cmdParms[1].Value = model.remarks;
            cmdParms[2].Value = model.updatetime;
            cmdParms[3].Value = model.id;
            return (DbHelperSQL.ExecuteSql(builder.ToString(), cmdParms) > 0);
        }

        public bool UpdateBBS(int id)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("update " + this.databaseprefix + "bbs set ");
            builder.Append("updatetime=@updatetime");
            builder.Append(" where id=@id");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@updatetime", SqlDbType.DateTime), new SqlParameter("@id", SqlDbType.Int, 4) };
            cmdParms[0].Value = DateTime.Now;
            cmdParms[1].Value = id;
            return (DbHelperSQL.ExecuteSql(builder.ToString(), cmdParms) > 0);
        }
    }
}
